package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT sum(amount) FROM order_info WHERE create_time>=\"2023-01-06 00:00:00\" and create_time<=\"2023-01-06 23:59:59\"")
    double get1();

    @Select("call tt")
    List<Sugar> get2();

    @Select("SELECT a.*,RANK() over(PARTITION by a.name ORDER BY a.distance) rak FROM\n" +
            "(SELECT o.org_name name,COUNT(org_name)num,sum(actual_distance) distance,sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time,(SUM(actual_distance)/COUNT(org_name)) avgDistance,\n" +
            "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/COUNT(org_name)) avgNum FROM transport_task t LEFT JOIN line_base_info l ON t.line_id=l.id LEFT JOIN base_organ o ON l.org_id=o.id GROUP BY o.org_name )a\n")
    List<Sugar1> get3();

    @Select("SELECT r1.`name`,count(d.sorter_emp_id) value FROM\n" +
            "\ttransport_task_detail d\n" +
            "\tINNER JOIN\n" +
            "\ttransport_task t\n" +
            "\tON \n" +
            "\t\td.transport_task_id = t.id\n" +
            "\tINNER JOIN\n" +
            "\tline_base_info b\n" +
            "\tON \n" +
            "\t\tt.line_id = b.id\n" +
            "\tINNER JOIN\n" +
            "\tbase_organ o\n" +
            "\tON \n" +
            "\t\tb.org_id = o.id\n" +
            "\tINNER JOIN\n" +
            "\tbase_region_info r\n" +
            "\tON \n" +
            "\t\to.region_id = r.id\n" +
            "\tINNER JOIN\n" +
            "\tbase_region_info r1\n" +
            "\tON \n" +
            "\t\tr.parent_id = r1.id\n" +
            "\t\tGROUP BY r1.name\n" +
            "\n" +
            "\n" +
            "\n")
    List<Sugar> get4();
}
